package cfca.seal.dao.main.impl;

import cfca.seal.bean.Seal;
import cfca.seal.bean.SealUser;
import cfca.seal.common.Page;
import cfca.seal.dao.PageDao;
import cfca.seal.dao.SmartDao;
import cfca.seal.dao.main.ISealDao;
import cfca.seal.exception.CodeException;
import cfca.seal.system.Env;
import cfca.seal.util.StringUtil;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class SealDao
  implements ISealDao
{
  private static String tableName = "印章";
  private static final Logger log = LoggerFactory.getLogger("web_sys");

  @Resource
  private SmartDao smartDao;

  @Resource
  private PageDao pageDao;

  public boolean insert(Seal seal) throws CodeException { try { String sql = "INSERT INTO SEAL (ID, CODE, NAME, TYPE, ORGANIZATION_ID, SEAL_IMAGE_ID, CERT_TYPE, SEAL_CERT_ID, SEAL_PWD, SEAL_PFX_DATA, SEAL_PFX_PWD, SEAL_IMAGE_DATA, IMAGE_WIDTH, IMAGE_HEIGHT, SEAL_DATA, STATUS, CREATOR, CREATE_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null)";

      Object[] args = { seal.getId(), seal.getCode(), seal.getName(), Integer.valueOf(seal.getType()), seal.getOrganizationId(), seal.getSealImageId(), Integer.valueOf(seal.getCertType()), seal.getSealCertId(), seal.getSealPwd(), seal.getSealPfxData(), seal.getSealPfxPwd(), seal.getSealImageData(), Integer.valueOf(seal.getImageWidth()), Integer.valueOf(seal.getImageHeight()), seal.getSealData(), Integer.valueOf(seal.getStatus()), seal.getCreator(), new Date() };

      log.debug("insert seal sql: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1002", tableName + "表插入失败", e);
    } }

  public boolean update(Seal seal) throws CodeException
  {
    try
    {
      String sql = "UPDATE SEAL SET SEAL_IMAGE_ID = ?, ORGANIZATION_ID = ?, NAME = ?, TYPE = ?, CERT_TYPE = ?, SEAL_CERT_ID = ?, SEAL_PWD = ?, SEAL_PFX_DATA = ?, SEAL_PFX_PWD = ?, SEAL_IMAGE_DATA = ?, IMAGE_WIDTH = ?, IMAGE_HEIGHT = ?, SEAL_DATA = ?, STATUS = ?, CREATOR = ?, UPDATE_TIME = ? WHERE CODE = ?";

      Object[] args = { seal.getSealImageId(), seal.getOrganizationId(), seal.getName(), Integer.valueOf(seal.getType()), Integer.valueOf(seal.getCertType()), seal.getSealCertId(), seal.getSealPwd(), seal.getSealPfxData(), seal.getSealPfxPwd(), seal.getSealImageData(), Integer.valueOf(seal.getImageWidth()), Integer.valueOf(seal.getImageHeight()), seal.getSealData(), Integer.valueOf(seal.getStatus()), seal.getCreator(), new Date(), seal.getCode() };

      log.debug("update seal sql by code: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1003", tableName + "表更新失败", e);
    }
  }

  public boolean deleteById(String id) throws CodeException
  {
    try {
      String sql = "DELETE FROM SEAL WHERE ID = ?";
      Object[] args = { id };
      log.debug("delete seal sql by id: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1004", tableName + "表删除失败", e);
    }
  }

  public Seal getSealById(String id) throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL WHERE ID = ?";
      Object[] args = { id };
      log.debug("select seal sql by id: " + sql + args);
      return (Seal)this.smartDao.getBean(Seal.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public Seal getSealByCode(String code) throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL WHERE CODE = ?";
      Object[] args = { code };
      log.debug("select seal sql by code: " + sql + args);
      return (Seal)this.smartDao.getBean(Seal.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getAll() throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL";
      log.debug("select all seal sql: " + sql);
      return this.smartDao.getList(Seal.class, sql);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public boolean updateStatus(String id, String status) throws CodeException
  {
    try {
      String sql = "UPDATE SEAL SET STATUS = ?, UPDATE_TIME = ? WHERE ID = ?";
      Object[] args = { status, new Date(), id };
      log.debug("update seal sql by id: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1003", tableName + "表更新失败", e);
    }
  }

  public boolean removeBind(String id) throws CodeException
  {
    try {
      String sql = "DELETE FROM SEAL_USER WHERE SEAL_ID = ?";
      Object[] args = { id };
      log.debug("delete seal_user sql by seal_id: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1004", tableName + "表删除失败", e);
    }
  }

  public boolean bindUser(List<SealUser> sealUser) throws CodeException
  {
    try {
      String sql = "INSERT INTO SEAL_USER (ID, SEAL_ID, USER_ID, CREATE_TIME) VALUES (?, ?, ?, ?)";
      List batchArgs = new ArrayList();
      for (SealUser su : sealUser) {
        Object[] arg = { su.getId(), su.getSealId(), su.getUserId(), su.getCreateTime() };
        batchArgs.add(arg);
      }

      log.debug("insert seal_user sql: " + sql + batchArgs);
      int[] iResult = this.smartDao.batchUpdate(sql, batchArgs);

      for (int result : iResult) {
        if (result <= 0)
          return false;
      }
      return true;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1002", tableName + "表插入失败", e);
    }
  }

  public List<String> getBindUserId(String id) throws CodeException
  {
    try {
      String sql = "SELECT USER_ID FROM SEAL_USER WHERE SEAL_ID = ?";
      Object[] args = { id };
      log.debug("select seal_user sql by seal_id: " + sql + args);
      return this.smartDao.getJdbcTemplate().queryForList(sql, String.class, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getByCount(int cachesize) throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL LIMIT 0," + cachesize;
      log.debug("select seal sql limit: " + sql);
      return this.smartDao.getList(Seal.class, sql);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public Page<Seal> getPage(Seal seal, String pageNo, String orgid) throws CodeException
  {
    try {
      List conditionList = new ArrayList();
      List args = new ArrayList();

      if (StringUtil.isNotEmpty(orgid)) {
        conditionList.add("ORGANIZATION_ID = ?");
        args.add(orgid);
      } else if (StringUtil.isNotEmpty(seal.getOrganizationId())) {
        conditionList.add("ORGANIZATION_ID = ?");
        args.add(seal.getOrganizationId());
      }

      if (StringUtil.isNotEmpty(seal.getCode())) {
        conditionList.add("CODE = ?");
        args.add(seal.getCode());
      }

      if (StringUtil.isNotEmpty(seal.getName())) {
        conditionList.add("NAME like ?");
        args.add("%" + seal.getName() + "%");
      }

      if (StringUtil.isNotEmpty(seal.getStartTime())) {
        if ("sqlite".equals(Env.databaseType)) {
          conditionList.add("DATETIME(substr(CREATE_TIME,0,11), 'unixepoch', 'localtime') >= DATETIME(?)");
          args.add(seal.getStartTime());
        } else if ("mysql".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= date_format(?,'%y-%m-%d %H:%i:%s')");
          args.add(seal.getStartTime());
        } else if ("oracle".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= to_date(?,'yyyy-mm-dd HH24:MI:SS')");
          args.add(seal.getStartTime());
        } else if ("db2".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
          args.add(seal.getStartTime());
        }
      }

      if (StringUtil.isNotEmpty(seal.getEndTime())) {
        if ("sqlite".equals(Env.databaseType)) {
          conditionList.add("DATETIME(substr(CREATE_TIME,0,11), 'unixepoch', 'localtime') <= DATETIME(?)");
          args.add(seal.getEndTime());
        } else if ("mysql".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= date_format(?,'%y-%m-%d %H:%i:%s')");
          args.add(seal.getEndTime());
        } else if ("oracle".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= to_date(?,'yyyy-mm-dd HH24:MI:SS')");
          args.add(seal.getEndTime());
        } else if ("db2".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
          args.add(seal.getEndTime());
        }
      }

      return this.pageDao.dispage(Seal.class, "SEAL", conditionList, args.toArray(), pageNo, 20);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getAllCanUsed(String orgid) throws CodeException
  {
    try
    {
      String sql = "SELECT * FROM SEAL WHERE STATUS = 0";
      if ((orgid != null) && (!orgid.isEmpty()))
        sql = sql + " AND ORGANIZATION_ID = '" + orgid + "'";
      log.debug("select seal sql by status=0: " + sql);
      return this.smartDao.getList(Seal.class, sql);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getAllCanUsedForShow(String orgid) throws CodeException
  {
    try {
      String sql = "SELECT ID, CODE, NAME FROM SEAL WHERE STATUS = 1 AND CERT_TYPE <> -1";
      if ((orgid != null) && (!orgid.isEmpty()))
        sql = sql + " AND ORGANIZATION_ID = '" + orgid + "'";
      log.debug("select seal(id, code, name) sql by status=0 and cert_type<>-1: " + sql);
      return this.smartDao.getList(Seal.class, sql);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getSealByCertDnAndCertSn(String certDN, String certSN) throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL WHERE ID IN(SELECT SEAL_ID FROM SEAL_USER WHERE USER_ID=(SELECT ID FROM USERS WHERE CERT_DN=? AND CERT_SN=?))";
      Object[] args = { certDN, certSN };
      log.debug("select getSealByCertDnAndCertSn(certDN, certSn) sql: " + sql + args);
      return this.smartDao.getList(Seal.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getSealBySealCertId(String id) throws Exception
  {
    try {
      String sql = "SELECT * FROM SEAL WHERE SEAL_CERT_ID = ?";
      Object[] args = { id };
      log.debug("select seal sql by id: " + sql + args);
      return this.smartDao.getList(Seal.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }
}